import java.sql.*;
import java.util.*;

public class DbComparator {
    public CompareResult compare(Db db1, Db db2) throws SQLException {
        // TODO: Implement this method
        //连接数据库
        Connection conn1 = DriverManager.getConnection(db1.getUrl(),db1.getUsername(), db1.getPassword());
        DatabaseMetaData metaData1 = conn1.getMetaData();
        String sql1 = "SHOW TABLES";
        Statement stat1 =conn1.createStatement();
        ResultSet rs1 = stat1.executeQuery(sql1);
        List<String> tables1 = new ArrayList<>();
        while (rs1.next()){
            tables1.add(rs1.getString(1));
        }

        Connection conn2 = DriverManager.getConnection(db2.getUrl(),db2.getUsername(), db2.getPassword());
        DatabaseMetaData metaData2 = conn2.getMetaData();
        String sql2 = "SHOW TABLES";
        Statement stat2 =conn2.createStatement();
        ResultSet rs2 = stat2.executeQuery(sql2);
        List<String> tables2 = new ArrayList<>();
        while (rs2.next()){
            tables2.add(rs2.getString(1));
        }
        CompareResult compareResult = new CompareResult();

        //比较两个数据库的表结构差异
        for(String table1 : tables1){
            TableDiff tableDiff =new TableDiff();
            tableDiff.setName(table1);
            if(!tables2.contains(table1)){
                tableDiff.setDiff(3);
            }else {
                //比较两张表结构
                ResultSet rsTable1Columns1 = metaData1.getColumns(null, null, table1, null);
                ResultSet rsTable2Columns2 = metaData2.getColumns(null, null, table1, null);
                Map<String,TableContent> tableContentMap1 = new HashMap<>();
                Map<String,TableContent> tableContentMap2 = new HashMap<>();
                while (rsTable1Columns1.next()) {
                    TableContent tableContent = new TableContent();
                    tableContent.setColumnType(rsTable1Columns1.getString("TYPE_NAME"));
                    tableContent.setDatasize(rsTable1Columns1.getInt("COLUMN_SIZE"));
                    tableContent.setNullable(rsTable1Columns1.getInt("NULLABLE"));
                    tableContent.setColumnName(rsTable1Columns1.getString("COLUMN_NAME"));
                    tableContent.setColumnDefault(rsTable1Columns1.getString("COLUMN_DEFAULT"));
                    tableContent.setColumnKey(rsTable1Columns1.getInt("COLUMN_KEY"));
                    tableContent.setExtra(rsTable1Columns1.getInt("EXTRA"));
                    tableContentMap1.put(tableContent.getColumnName(),tableContent);
                }
                while (rsTable2Columns2.next()){
                    TableContent tableContent = new TableContent();
                    tableContent.setColumnType(rsTable2Columns2.getString("TYPE_NAME"));
                    tableContent.setDatasize(rsTable2Columns2.getInt("COLUMN_SIZE"));
                    tableContent.setNullable(rsTable2Columns2.getInt("NULLABLE"));
                    tableContent.setColumnName(rsTable2Columns2.getString("COLUMN_NAME"));
                    tableContent.setColumnDefault(rsTable2Columns2.getString("COLUMN_DEFAULT"));
                    tableContent.setColumnKey(rsTable2Columns2.getInt("COLUMN_KEY"));
                    tableContent.setExtra(rsTable2Columns2.getInt("EXTRA"));
                    tableContentMap2.put(tableContent.getColumnName(),tableContent);
                }
                boolean a =true;
                for(Map.Entry<String,TableContent> entry : tableContentMap1.entrySet()){
                    ColumnDiff columnDiff = new ColumnDiff();
                    columnDiff.setTable(table1);
                    columnDiff.setName(entry.getKey());
                    if(tableContentMap2.containsKey(entry.getKey())){
                        TableContent tableContent1 = entry.getValue();
                        TableContent tableContent2 = tableContentMap2.get(entry.getKey());
                        if(!tableContent1.equals(tableContent2)){
                            a = false;
                            columnDiff.setDiff(2);
                        }else {
                            columnDiff.setDiff(1);
                        }
                        tableContentMap2.remove(entry.getKey());
                    }else {
                        a = false;
                        columnDiff.setDiff(1);
                        
                    }
                    compareResult.getColumnDiffs().add(columnDiff);
                    tableContentMap1.remove(entry.getKey());
                }
                if(!a){
                    tableDiff.setDiff(2);
                }else {
                    tableDiff.setDiff(1);
                }
            }
            compareResult.getTableDiffs().add(tableDiff);
        }

        for(String table2 : tables2){
            TableDiff tableDiff =new TableDiff();
            if(!tables1.contains(table2)){
                tableDiff.setName(table2);
                tableDiff.setDiff(4);
            }
            compareResult.getTableDiffs().add(tableDiff);
        }

        return compareResult ;

    }
}
